import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.jdbc.aq.AQNotificationRegistration;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleDriver;

import org.apache.log4j.Logger;


public class OracleAQ {
	
	protected Logger logger = Logger.getLogger( OracleAQ.class);
	
	protected String user;
	private String password;
	private String host;
	private String port;
	private String sid;
	private String url;
	protected OracleConnection conn;
	private Statement stmt;
	protected CallableStatement cstmt;
	
	public static int PAYLOAD_TYPE_NORMAL = 1;
	public static int PAYLOAD_TYPE_RAW = 2;
	
	public OracleAQ() {}
	
	public OracleAQ( String user, String password, String host, String port, String sid) {
		this.user = user;
		this.password = password;
		this.host = host;
		this.port = port;
		this.sid = sid;
		this.url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=" + this.host + ")(PORT=" + this.port + "))(CONNECT_DATA=(SERVICE_NAME=" + this.sid + ")))";
		
	}
	
	public OracleAQ( String user, String password, String url) {
		this.user = user;
		this.password = password;
		this.url = url;
		
	}
	
	protected OracleConnection connect() {
		
		try {
			OracleDriver dr = new OracleDriver();
			Properties props = new Properties();
			props.setProperty( "user", this.user);
			props.setProperty( "password", this.password);
			
			this.conn = ( OracleConnection)dr.connect( this.url, props);
			logger.info( "DB connected.");
			
		} catch( Exception e) {
//			e.printStackTrace();
			logger.error( e.getMessage());
			
		}
		
		return this.conn;
	}
	
	protected void disconnect() {
		
		if( this.stmt != null) {
			try { 
				this.stmt.close();
				
			} catch( Exception e) {
//				e.printStackTrace();
				logger.error( e.getMessage());
				
			}
		}
		
		if( this.conn != null) {
			try {
				this.conn.close();
				logger.info( "DB disconnected.");
				
			} catch( Exception e) {
//				e.printStackTrace();
				logger.error( e.getMessage());
				
			}
		}
		
	}
	
	protected int doUpdateDatabase( String sql) throws SQLException {
		
		try {
			this.stmt = this.conn.createStatement();
			this.stmt.execute( sql);
			
		} catch( Exception e) {
			e.printStackTrace();
			return 1;
			
		}
		
		return 0;
	
	}
	
	protected void doUpdateDatabase( Connection conn, String sql) throws SQLException {
		Statement stmt = null;
		
		try {
			stmt = conn.createStatement();
			stmt.execute( sql);
		}
		finally {
			if( stmt != null) {
				try {
					stmt.close();
				} catch ( SQLException ex) {}
			}
		}
	}
	
	protected AQNotificationRegistration registerForAQEvents( OracleConnection conn, String queueName) throws SQLException {
		Properties globalOptions = new Properties();
		
		String[] queueNameArr = new String[1];
		queueNameArr[0] = queueName;
		
		Properties[] opt = new Properties[1];
		opt[0] = new Properties();
		opt[0].setProperty( oracle.jdbc.OracleConnection.NTF_AQ_PAYLOAD, "true");
		
		AQNotificationRegistration[] regArr = conn.registerAQNotification( queueNameArr, opt, globalOptions);
		AQNotificationRegistration reg = regArr[0];
		
		return reg;
		
	}
	
	public static void main( String[] argv) {

		OracleAQ oa = new OracleAQ( "scott", "tiger", "bizteco.com", "21521", "nams1");
		oa.connect();
		oa.disconnect();
	
	}

}
